package com.zha;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.etc.User;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**原文档
 * id    name   sex  num
 * 1	小明	男	1000
 * 2	张三	女	840
 * 3	李四	男	440
 * 4	王五	男	560
 */
public class fds {
    public static void main(String[] args) throws IOException, SQLException {
        //上面的文档在windos本地的路径
             String fileName="F:\\excle\\book.xlsx";
             //从本地读取文件
             FileInputStream fis = new FileInputStream(fileName);
             //workbook工作簿接口，用来做excle表格的一系列操作
             Workbook workbook = null;
             //判断excel的两种格式xls,xlsx
             if(fileName.toLowerCase().endsWith("xlsx")){
                     workbook = new XSSFWorkbook(fis);
              }else if(fileName.toLowerCase().endsWith("xls")){
                     workbook = new HSSFWorkbook(fis);
              }
                //得到sheet的总数，有几页excle表
                int numberOfSheets = workbook.getNumberOfSheets();
//                 List<User> userList = new ArrayList<User>();
              //循环每一个sheet
                for(int i=0; i < numberOfSheets; i++){
                          //得到第i个sheet
                          Sheet sheet = workbook.getSheetAt(i);
                          //得到行的迭代器
                          Iterator<Row> rowIterator = sheet.iterator();
                          //循环每一行
                          while (rowIterator.hasNext()){
                              User user = new User();
                              //得到一行对象
                              Row row = rowIterator.next();
                              int rowNum = row.getRowNum();
                              if (rowNum == 0){
                                  continue;
                              }
                              //得到列对象
                              Iterator<Cell> cellIterator = row.cellIterator();
                              int number = 0;
                              while ( cellIterator.hasNext() ){
                                  Cell next = cellIterator.next();
                                  if (number == 0){
                                      double numericCellValue = next.getNumericCellValue();
                                      user.setId(String.valueOf(numericCellValue));
                                      number++;
                                      continue;
                                  }
                                  if (number == 1){
                                      String name = next.getStringCellValue();
                                      user.setName(name);
                                      number++;
                                      continue;
                                  }
                                  if (number == 2){
                                      String sex = next.getStringCellValue();
                                      user.setSex(sex);
                                      number++;
                                      continue;
                                  }
                                  if (number == 3){
                                      double num = next.getNumericCellValue();
                                      user.setNum(String.valueOf(num));
                                      number++;
                                      continue;
                                  }
                              }
                              add(user);
                              }
                            System.out.println();
                        }

                        fis.close();
            }

            public static void add(User user) throws SQLException {
                Connection connection = JDBCutils.getConnection();
                String sql = "insert into student value(?,?,?,?)";
                PreparedStatement statement = connection.prepareStatement(sql);
                String id = user.getId();
                double v = Double.parseDouble(id);
                int a = (int)v;
                statement.setInt(1,a);
                statement.setString(2,user.getName());
                statement.setString(3,user.getSex());
                statement.setInt(4,(int)Double.parseDouble(user.getNum()));
                int i = statement.executeUpdate();
                System.out.println(i == 1 ? "success":"fail");
            }
    }

